﻿using KPIS.GERP.Models.Class;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using KPIS.GERP.Models.ICM;
using Resources;
using System.Text;
using KPIS.GERP.Models.ICM.ViewModel;
using System.Data;
//Adding on 26/5/57
using KPIS.GERP.Models.HRM;
/* OpenXml */
using System.Drawing;
using Novacode;
/* OpenXml */

namespace KPIS.GERP.WEB.GIMS.Controllers
{
    // [AccessDeniedAuthorize]
    public class PurchaseOrderController : Controller
    {
	    private string[] _basicIncludeSubclass = new[]
		    {
                "MasPurchaseType", "Progress", "Bidder","Bidder.PurchaseRequest","Bidder.PurchaseRequest.MasPurchaseMethod","Bidder.MasContact"
		    };

        public ActionResult Index()
        {
            //ViewBag.Page = page;
            var service = new PurchaseOrderService.PurchaseOrderServiceClient();
            return View(service.Select(new Models.ICM.PurchaseOrder { RecordStatus = "!D" }, _basicIncludeSubclass));
        }

        public ActionResult Create()
        {
            try
            {
                //List<Bidder> bddlst = null;
                //using (var service = new BidService.BidServiceClient())
                //{
                //    bddlst = service.SelectBidder(new Models.ICM.Bidder { }, new[] { "PurchaseRequest", "MasContact" }).Where(p => p.PrId != null && p.WinnerStatus == "1" && p.PurchaseRequest.RecordStatus == RecordStatus.ApprovedStatus).ToList();
                //   // bddlst = service.SelectBidder(new Models.ICM.Bidder { }, new[] { "PurchaseRequest", "MasContact" }).Where(p => p.PrId != null && p.WinnerStatus == "1" && p.PurchaseRequest.RecordStatus == RecordStatus.ApprovedStatus).ToList();
                //}
                //vmBidderVsPO vm = new vmBidderVsPO();
                //vm.Bidders = bddlst;
                //vm.PurchaseOrder = null;
                List<Bidder> lst = null;
                using (var service = new PurchaseRequestService.PurchaseRequestServiceClient())
                {
                    lst = service.SelectNotPo().ToList();
                    //bddlst = service.SelectBidder(new Models.ICM.Bidder { }, new[] { "PurchaseRequest", "MasContact" }).Where(p => p.PrId != null && p.WinnerStatus == "1" && p.PurchaseRequest.RecordStatus == RecordStatus.ApprovedStatus).ToList();
                }
                var lst2 = lst.GroupBy(p => p.PrId)
                                .Select(g => g.First())
                            .ToList();
                var po = new PurchaseOrder();
                po.OrderDate = DateTime.Today;
                po.IsContract = false;
                vmPrVsPo vm = new vmPrVsPo();
                vm.Bidders = lst2;
                vm.PurchaseOrder = po;

                return View(vm);
            }
            catch (Exception ex)
            {
                GerpLog.Error(ErrorLocalization.ErrorWhileDeleteData + ", " + ex.Message);
                return RedirectToAction("Details");
            }
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create(vmPrVsPo model, int[] selectIds, FormCollection collection)
        {
            int PoId = 0;
            StringBuilder message_error = new StringBuilder();
            message_error.Append("<div class='alert alert-error'>");
            message_error.Append("<button type='button' class='close' data-dismiss='alert'>");
            message_error.Append("<i class='icon-remove'></i>");
            message_error.Append("</button>");
            message_error.Append("<strong>");
            message_error.Append("<i class='icon-remove'></i>");
            message_error.Append(" พบข้อผิดพลาด: ");
            message_error.Append("</strong>");

            if (model == null)
            {
                message_error.Append("กรอกข้อมูลไม่ครบถ้วน!");
                message_error.Append("</div>");
                return Json(new { selectIds = selectIds, success = false, message = message_error.ToString() });
            }
           // throw new System.ArgumentNullException("Parameter cannot be null", "original");
            try
            {
                if (selectIds == null) {                   
                    message_error.Append("กรุณาเลือกรายการขอซื้อขอจ้าง!");
                    message_error.Append("</div>");
                    return Json(new { selectIds = selectIds, success = false, message = message_error.ToString() });
                }
                else if (model.PurchaseOrder.OrderDate == null)
                {
                    message_error.Append("กรุณาเลือกวันที่ทำรายการ");
                    message_error.Append("</div>");
                    return Json(new { selectIds = selectIds, success = false, message = message_error.ToString() });
                }
                else if (String.IsNullOrWhiteSpace(model.PurchaseOrder.Name))
                {
                    message_error.Append("กรุณากรอกหัวข้อเรื่องใบสั่ง");
                    message_error.Append("</div>");
                    return Json(new { selectIds = selectIds, success = false, message = message_error.ToString() });
                }
                else if (String.IsNullOrWhiteSpace(model.PurchaseOrder.DurTypeId.ToString()))
                {
                    message_error.Append("กรุณาเลือกระยะเวลาส่ง");
                    message_error.Append("</div>");
                    return Json(new { selectIds = selectIds, success = false, message = message_error.ToString() });                   
                }
                else if (String.IsNullOrWhiteSpace(model.PurchaseOrder.CanInstallment))
                {
                    message_error.Append("กรุณาเลือกการปรับ");
                    message_error.Append("</div>");
                    return Json(new { selectIds = selectIds, success = false, message = message_error.ToString() });
                }
                else
                {
                    //copy data from pr bidder to po
                    ///////not define data yet///////
                    //po.DateReturnContract; เช็คจากไหนหรือต้องไปดึข้อมูลจากไหนตอนทำ po
                    //po.DateReturnPrepay;  เช็คจากไหนหรือต้องไปดึข้อมูลจากไหนตอนทำ po
                    //po.DateReturnRedemption;  เช็คจากไหนหรือต้องไปดึข้อมูลจากไหนตอนทำ po
                    //po.DateStartCount;  เช็คจากไหนหรือต้องไปดึข้อมูลจากไหนตอนทำ po                             
                    //po.NotSeparateReason; เช็คจากไหนหรือต้องไปดึข้อมูลจากไหนตอนทำ po
                    int BidderId = selectIds[0];
                    PurchaseOrder po = new PurchaseOrder();
                    using (var serviceBid = new BidService.BidServiceClient())
                    {
                        var service = new PurchaseOrderService.PurchaseOrderServiceClient();
                        var servicePOItem = new PurchaseOrderItemService.PurchaseOrderItemServiceClient();

                        var bidder = serviceBid.SelectBidder(new Bidder { BidderId = BidderId }, new[] { "PurchaseRequest" }).FirstOrDefault();
                        po.BidderId = BidderId;
                        po.BgYear = bidder.PurchaseRequest.BgYear;
                        po.PcTypeId = bidder.PurchaseRequest.PcTypeId;
                        po.YearTypeId = bidder.PurchaseRequest.YearTypeId;
                        po.ProgressId = 31;
                        po.Code = service.GenNewPo(DateTime.Now.Year, DateTime.Now.Month);
                        po.Descr = model.PurchaseOrder.Descr;
                        po.DurTypeId = model.PurchaseOrder.DurTypeId;
                        po.Name = model.PurchaseOrder.Name;
                        po.OrderDate = model.PurchaseOrder.OrderDate;
                        po.CanInstallment = model.PurchaseOrder.CanInstallment;
                        po.CanSeparateDetail = model.PurchaseOrder.CanSeparateDetail;
                        po.PrepayAmount = model.PurchaseOrder.PrepayAmount;
                        po.RedemptionPercent = model.PurchaseOrder.RedemptionPercent;
                        po.IsContract = model.PurchaseOrder.IsContract;

                        if (po.DurTypeId == 12)
                        {
                            po.RequireDuration = model.PurchaseOrder.RequireDuration;
                            po.DeliveryDate = (po.OrderDate ?? DateTime.Today).AddDays((int)po.RequireDuration);
                            po.RequireDate = po.DeliveryDate;
                        }
                        else
                        {
                            po.RequireDate = model.PurchaseOrder.RequireDate;
                            po.DeliveryDate = po.RequireDate;
                        }

                        PoId = service.Insert(po, Class.GimsMembership.CurrentEmpId);

                        var bidderQuos = serviceBid.SelectBidderQuotation(new BidderQuotation() { BidderId = BidderId, Selected = "1" }, null);

                        foreach (var bidderQuo in bidderQuos)
                        {
                            PurchaseOrderItem poitem  = new PurchaseOrderItem();

                            poitem.BidderQuoId = bidderQuo.BidderQuoId;
                            poitem.PoId = PoId;
                            servicePOItem.Insert(poitem, Class.GimsMembership.CurrentEmpId);
                        }
                    }
                    #region oldcode
                    //var servicePO = new PurchaseOrderService.PurchaseOrderServiceClient();

                    //using (var serviceBdd = new BidService.BidServiceClient())
                    //{
                    //    var bddlst = serviceBdd.SelectBidder(new Bidder { PrId = prId, WinnerStatus = "1" }, new[] { "MasContact" });
                    //    foreach (Bidder item in bddlst)
                    //    {
                    //        po.BidderId = item.BidderId;

                    //        //po.Gfmis = item.MasContact.GfmisCode;
                    //    }
                    //}
                    //if (po.IsContract == null)
                    //{

                    //    PurchaseOrderInstallment poi = new PurchaseOrderInstallment();
                    //    // poi.CreatedBy = Class.GimsMembership.CurrentEmpId;
                    //    // poi.CreatedWhen = DateTime.Now;
                    //    poi.DeliveryDate = po.DeliveryDate;
                    //    poi.DurationType = po.DurationType;
                    //    poi.DurTypeId = po.DurTypeId;
                    //    poi.InstallNumber = 1;
                    //    poi.PoId = ret;
                    //    poi.RequireDate = po.RequireDate;
                    //    poi.RequireDuration = po.RequireDuration;

                    //    using (var serviceq = new PurchaseOrderService.PurchaseOrderServiceClient())
                    //    {
                    //        serviceq.InsertPoInstallment(poi, Class.GimsMembership.CurrentEmpId);
                    //    }
                    //}
                    //else {
                    //    if (!(bool)po.IsContract)
                    //    {

                    //        PurchaseOrderInstallment poi = new PurchaseOrderInstallment();
                    //        // poi.CreatedBy = Class.GimsMembership.CurrentEmpId;
                    //        // poi.CreatedWhen = DateTime.Now;
                    //        poi.DeliveryDate = po.DeliveryDate;
                    //        poi.DurationType = po.DurationType;
                    //        poi.DurTypeId = po.DurTypeId;
                    //        poi.InstallNumber = 1;
                    //        poi.PoId = ret;
                    //        poi.RequireDate = po.RequireDate;
                    //        poi.RequireDuration = po.RequireDuration;

                    //        using (var serviceq = new PurchaseOrderService.PurchaseOrderServiceClient())
                    //        {
                    //            serviceq.InsertPoInstallment(poi, Class.GimsMembership.CurrentEmpId);
                    //        }
                    //    }
                    //}

                   
                    //copy data from pritem to poitem
                    //if (ret > 0)
                    //{
                    //var pritem = servicePRItem.Select(new PurchaseRequestItem { PrId = prId }, null);
                    //foreach (PurchaseRequestItem item in pritem)
                    //{
                    //    var serviceBidderQuo = new BidService.BidServiceClient();
                    //    var bidderquo = serviceBidderQuo.SelectBidderQuotation(new BidderQuotation { PrItemId = item.PrItemId }, null);
                            
                    //    PurchaseOrderItem poitem = new PurchaseOrderItem();
                    //    poitem.BidderQuoId = bidderquo.FirstOrDefault() == null ? null : (int?)bidderquo.FirstOrDefault().BidderQuoId;
                    //    //poitem.PoId = ret;

                    //    var servicePOItem = new PurchaseOrderItemService.PurchaseOrderItemServiceClient();
                    //    servicePOItem.Insert(poitem, Class.GimsMembership.CurrentEmpId);
                    //}
                            
                        ////back to update pr status
                        //var servicePR = new PurchaseRequestService.PurchaseRequestServiceClient();
                        //List<PurchaseRequest> pr = servicePR.Select(new PurchaseRequest{ PrId = prId },null).ToList();
                        //pr.ForEach(l => new Action( delegate(){ l.RecordStatus = RecordStatus.UsedStatus; l.ProgressId = 31; }).Invoke());
                        //int rowAffected = servicePR.UpdateRet(pr.FirstOrDefault(), Class.GimsMembership.CurrentEmpId);
                        //if (rowAffected > 0) {
                        //    pritem.ToList().ForEach(p => p.RecordStatus = RecordStatus.UsedStatus);
                        //    foreach (PurchaseRequestItem pritemm in pritem) 
                        //        servicePRItem.Update(pritemm,Class.GimsMembership.CurrentEmpId);                             
                        //}
                    //}
                    #endregion
                    message_error = new StringBuilder();
                    message_error.Append("<div class='alert alert-block alert-success'>");
                    message_error.Append("<button type='button' class='close' data-dismiss='alert'>");
                    message_error.Append("<i class='icon-remove'></i>");
                    message_error.Append("</button>");
                    message_error.Append("<strong>");
                    message_error.Append("<i class='icon-ok'></i>");
                    message_error.Append(" บันทึกข้อมูล: ");
                    message_error.Append("</strong>");
                    message_error.Append("สร้างใบสั่งซื้อเรียบร้อยแล้ว");
                    message_error.Append("</div>");
                    return Json(new { PoId = PoId, success = true, message = message_error.ToString() });             
                }

            }catch(Exception ex){
                GerpLog.Error(ErrorLocalization.ErrorWhileDeleteData + ", " + ex.Message);
                ModelState.AddModelError(ErrorLocalization.Error, ErrorLocalization.ErrorWhileDeleteData);
                return Json(new { success = false });               
            }
        }

        public ActionResult Details(int id, bool? nolayout = false)
		{
            ViewBag.EmptyLayout = nolayout ?? false;
            try
            {
                using (var servicePo = new PurchaseOrderService.PurchaseOrderServiceClient())
                {
                    var polst = servicePo.Select(new PurchaseOrder { PoId = id }, _basicIncludeSubclass);
                    return View(polst.FirstOrDefault());
                }
            }
            catch (Exception ex)
            {
                GerpLog.Error(ErrorLocalization.ErrorWhileDeleteData + ", " + ex.Message);
                return View(new PurchaseOrder());
            }
		}

        public ActionResult Edit(int id)
        {
            try
            {
                using (var servicePo = new PurchaseOrderService.PurchaseOrderServiceClient())
                {
                    var polst = servicePo.Select(new PurchaseOrder { PoId = id }, new[] { "Bidder" });
                    vmPrVsPo vm = new vmPrVsPo();
                    vm.PurchaseOrder = polst.FirstOrDefault();
                    var service = new BidService.BidServiceClient();
                    vm.Bidders = service.SelectBidder(new Models.ICM.Bidder { BidderId = (int)polst.FirstOrDefault().BidderId }, new[] { "PurchaseRequest", "MasContact" }).ToList();
                    return View(vm);
                }
            }catch(Exception ex){
                GerpLog.Error(ErrorLocalization.ErrorWhileDeleteData + ", " + ex.Message);
                return View(new vmPrVsPo());
            }
           
        }

        [HttpPost]
        public ActionResult Edit(vmPrVsPo model, int[] selectIds)
        {
            try
            {
				if (ModelState.IsValid)
				{
                    using (var service = new PurchaseOrderService.PurchaseOrderServiceClient())
                    {
                        var polst = service.Select(new PurchaseOrder { PoId = model.PurchaseOrder.PoId }, new[] { "Bidder" }).ToList();
                        polst.ForEach(l => new Action(delegate()
                        {
                            l.IsContract = model.PurchaseOrder.IsContract;
                            l.CanInstallment = model.PurchaseOrder.CanInstallment;
                            l.OrderDate = model.PurchaseOrder.OrderDate; 
                            l.Name = model.PurchaseOrder.Name;
                            l.Descr = model.PurchaseOrder.Descr; 
                            l.DurTypeId = model.PurchaseOrder.DurTypeId;
                            //if (model.PurchaseOrder.DurTypeId == 12) 
                            //    l.RequireDuration = model.PurchaseOrder.RequireDuration;
                            //else 
                            //    l.RequireDate = model.PurchaseOrder.RequireDate;

                            if (l.DurTypeId == 12)
                            {
                                l.RequireDuration = model.PurchaseOrder.RequireDuration;
                                l.DeliveryDate = (l.OrderDate ?? DateTime.Today).AddDays((int)l.RequireDuration);
                                l.RequireDate = l.DeliveryDate;
                            }
                            else
                            {
                                l.RequireDate = model.PurchaseOrder.RequireDate;
                                l.DeliveryDate = l.RequireDate;
                            }
                        }).Invoke());
                        service.Update(polst.FirstOrDefault(), Class.GimsMembership.CurrentEmpId);
                    }
                    return RedirectToAction("Index");
                }
                return View(model);
			}
			catch (Exception ex)
            {
                GerpLog.Error(ErrorLocalization.ErrorWhileDeleteData + ", " + ex.Message);
                return View(model);
				//throw ex;
            }
        }

		public ActionResult Delete(int id)
		{
			try
			{
                
                using (var service = new PurchaseOrderService.PurchaseOrderServiceClient())
                {
                    service.Delete(id, Class.GimsMembership.CurrentEmpId);
                }
                return RedirectToAction("Index");
			}
			catch (Exception ex)
			{
                GerpLog.Error(ErrorLocalization.ErrorWhileDeleteData + ", " + ex.Message);
                ModelState.AddModelError("พบข้อผิดพลาด!", "ไม่สามารถลบข้อมูลได้");
                return View("Index");
			}
		}

		[HttpPost, ValidateAntiForgeryToken]
		public ActionResult DeleteAll(int[] deleteIds)
		{
            StringBuilder message_error = new StringBuilder();
            message_error.Append("<div class='alert alert-error'>");
            message_error.Append("<button type='button' class='close' data-dismiss='alert'>");
            message_error.Append("<i class='icon-remove'></i>");
            message_error.Append("</button>");
            message_error.Append("<strong>");
            message_error.Append("<i class='icon-remove'></i>");
            message_error.Append(" พบข้อผิดพลาด: ");
            message_error.Append("</strong>");
        

			if (deleteIds == null){
				 message_error.Append("กรุณาเลือกข้อมูลที่ต้องการลบ!");
                 message_error.Append("</div>");
                 return Json(new { deleteIds = deleteIds, success = false , message = message_error.ToString() });
            }

			try
			{
                using (var service = new PurchaseOrderService.PurchaseOrderServiceClient())
                {
                    foreach (int delete in deleteIds)
                    {                   
                        service.Delete(delete, Class.GimsMembership.CurrentEmpId);
                    }

                }

                message_error = new StringBuilder();
                message_error.Append("<div class='alert alert-block alert-success'>");
                message_error.Append("<button type='button' class='close' data-dismiss='alert'>");
                message_error.Append("<i class='icon-remove'></i>");
                message_error.Append("</button>");
                message_error.Append("<strong>");
                message_error.Append("<i class='icon-ok'></i>");
                message_error.Append(" ลบข้อมูล: ");
                message_error.Append("</strong>");
                message_error.Append("ลบข้อมูลเรียบร้อยแล้ว!");
                message_error.Append("</div>");
                ModelState.AddModelError(ErrorLocalization.Error, string.Format("ลบข้อมูลไปทั้งหมด {0} รายการ", deleteIds.Length.ToString("#,##0")));
                return Json(new { success = true, message = message_error.ToString() });
                //return Json(new { success = true });
			}
			catch (Exception ex)
			{
				ModelState.AddModelError(ErrorLocalization.Error, ErrorLocalization.ErrorWhileDeleteData);
				GerpLog.Error(ErrorLocalization.ErrorWhileDeleteData + ", " + ex.Message);
				return Json(new { success = false });
			}
		}

        public void ReportPoCotact(int PoId)
        {
            var service = new PurchaseOrderService.PurchaseOrderServiceClient();
            var model = service.Select(new Models.ICM.PurchaseOrder { RecordStatus = "!D" }, null).FirstOrDefault();
            var model1 = service.ContactPo(PoId);
            var model2 = service.TotalNetAmountPoItem(PoId);
            var model3 = service.CollateralPo(PoId);
            Dictionary<string, string> fields = new Dictionary<string, string>();
            Dictionary<string, DataTable> Table = new Dictionary<string, DataTable>();
            MailMerge mailMerge = new MailMerge();

            string strIfEmpty = ".................";

            fields.Add("PoCode", string.IsNullOrWhiteSpace(model.Code) ? strIfEmpty : model.Code);
            fields.Add("PoName", model.Name);
            fields.Add("ContactName", string.IsNullOrWhiteSpace(model1[0]) ? strIfEmpty : model1[0]);
            fields.Add("ContactAddress", string.IsNullOrWhiteSpace(model1[3]) ? strIfEmpty : model1[3]);
            fields.Add("CollateralTypeName", string.IsNullOrWhiteSpace(model3[4]) ? strIfEmpty : model3[4]);
            fields.Add("ReceiptNo", string.IsNullOrWhiteSpace(model3[2]) ? strIfEmpty : model3[2]);
            fields.Add("VatAmount", string.IsNullOrWhiteSpace(model1[0]) ? strIfEmpty : model1[0]);
            fields.Add("BankName", string.IsNullOrWhiteSpace(model1[1]) ? strIfEmpty : model1[1]);
            fields.Add("BranchName", string.IsNullOrWhiteSpace(model1[2]) ? strIfEmpty : model1[2]);
            fields.Add("BankAccountName", string.IsNullOrWhiteSpace(model1[4]) ? strIfEmpty : model1[4]);
            fields.Add("ApprovedDate", "..........");
            fields.Add("ApproverName", ".........");
            fields.Add("CollateralAmount", "...........");
            fields.Add("RequireDuration", string.IsNullOrWhiteSpace(model3[1]) ? strIfEmpty : model3[1]);
            fields.Add("PenaltyAmount", string.IsNullOrWhiteSpace(model3[6]) ? strIfEmpty : model3[6]);

            string strInstallmentDetails = "";
            for (int i = 0; i < model2.Length; i++)
            {
                var item = model2[i];

                if (i == model2.Length - 1)
                {
                }
                else
                {
                    string AmountText = service.ThaiBaht(item[4]);
                    strInstallmentDetails += "เป็นจำนวนเงิน " + ( string.IsNullOrWhiteSpace(item[4]) ? strIfEmpty : item[4] )
                        + "บาท ( " + (string.IsNullOrWhiteSpace(AmountText) ? strIfEmpty : AmountText) + " ) เมื่อผู้รับจ้าง" 
                        + " ได้ปฏิบัติงาน  ให้แล้วเสร็จภายใน" + (string.IsNullOrWhiteSpace(item[1]) ? strIfEmpty :item[1]) + System.Environment.NewLine;
                }
            }

            fields.Add("InstallmentDetails", strInstallmentDetails);

            Dictionary<string, bool> areas = new Dictionary<string, bool>();
             
            areas.Add("Area1", model.CanInstallment == "N");
            areas.Add("Area2", model.CanInstallment == "Y");

            string strTemplatePath = Server.MapPath("~/Templates/สัญญาจ้าง.docx");
            string strSaveAsPath = Server.MapPath("~/Temp/"); 
            string strSaveAsFileName = string.Format("สัญญาจ้าง_{1}_{0}.docx",
                DateTime.Now.ToString("yyyyMMddHHmmssffff"),
                model.Code);

            if (mailMerge.MergeTemplate(strTemplatePath, strSaveAsPath + strSaveAsFileName, fields, null, null, areas))
            {
                Response.Clear();
                Response.ContentType = "text/plain";
                Response.AddHeader("Content-Disposition", "attachment; filename=" + strSaveAsFileName);
                Response.WriteFile(strSaveAsPath + strSaveAsFileName);
                Response.Flush();
                if (true)
                {
                    System.IO.File.Delete(strSaveAsPath + strSaveAsFileName);
                }
                Response.End();
            }
        }

        public void ReportPoPurchase(int PoId)
        {
            var service = new PurchaseOrderService.PurchaseOrderServiceClient();
            var model = service.Select(new Models.ICM.PurchaseOrder { RecordStatus = "!D", PoId = PoId }, null).FirstOrDefault();
            var model1 = service.ContactPo(PoId);
            var model2 = service.PositionNamePo(PoId);
            var model3 = service.PoItems(PoId);

            Dictionary<string, string> fields = new Dictionary<string, string>();
            MailMerge mailMerge = new MailMerge();

            fields.Add("PoCode", model.Code);
            fields.Add("ContactName", model1[0]);
            fields.Add("CreatedBy",  model2[0]);
            fields.Add("PositionName", model2[1]);
            fields.Add("PoName", model2[2]);
            fields.Add("OrderDate", model2[3]);
            //Adding on 26/5/57
            fields.Add("RequireDuration", (model.RequireDuration != null ? model.RequireDuration.ToString() + " วัน":""));
            fields.Add("ReqDate",Convert.ToDateTime(model.RequireDate).ToLongDateString());

            var dics = service.PoItems(PoId).ToList();
            decimal dTotalPrice = 0;
            decimal dtotalVat = 0;
            decimal dtotalNet = 0;
            foreach (var dic in dics)
            {
                decimal dTotalPriceEach = Convert.ToDecimal(dic.Where(o => o.Key == "SumPrice").Select(o => o.Value).FirstOrDefault() ?? "0");
                decimal dTotalNetEach = Convert.ToDecimal(dic.Where(o => o.Key == "NetAmount").Select(o => o.Value).FirstOrDefault() ?? "0");
                dTotalPrice += dTotalPriceEach;
                dtotalVat += dTotalNetEach - dTotalPriceEach;
                dtotalNet += dTotalNetEach; 
            }

            var serviceUser = new EmployeeService.EmployeeServiceClient();
            var modelEmp = serviceUser.Select(new Employee { EmpId = Class.GimsMembership.CurrentEmpId }
                , new[] { "Position.WorkingUpperPosition" }, true).FirstOrDefault();
            //กำหนดชื่อหัวหน้าที่จะแสดงตามยอดเงินเพิ่ม ณ วันที่ 28/5/57
            var upperPositionId = (dtotalNet < 100000 ? modelEmp.Position.WorkingUpperPosId : (dtotalNet < 500000 ? 56 : 32));
            var modelEmpHead = serviceUser.Select(new Employee { PosId = upperPositionId, RecordStatus = "!D" }
                , new[] { "MasNamePrefix" }, true).FirstOrDefault();        //ใส่ new[] { "MasNamePrefix" } เพื่อให้ FullName มีคำนำหน้าชื่อมาด้วย    
            fields.Add("HeadName", (modelEmpHead.FullName ?? ""));            

            var userService = new UserService.UserServiceClient();
            var modelPosition = userService.GetUsers().Where(o => o.EmpId == modelEmpHead.EmpId).FirstOrDefault();
            var posName = modelPosition.Employee != null && modelPosition.Employee.Position != null && modelPosition.Employee.Position.Name != null ? modelPosition.Employee.Position.Name : "";
            fields.Add("HeadPositionName", upperPositionId == 78 ? "หัวหน้าเจ้าหน้าที่พัสดุ" : posName); //กำหนดไว้ชั่วคราวเนื่องจากคุณสุชาดา รหัสที่ 78 ตำแหน่งจริงเป็น หัวหน้างานไอที ระบบไม่สามารถกำหนดให้ 1 คนแสดง 2 ตำแหน่งได้

            decimal dVat = dtotalVat;//dTotalPrice * 7 / 100;
            decimal dIncludeVat = dTotalPrice + dVat;
            string ThaiBaht = service.ThaiBaht((dIncludeVat).ToString());

            fields.Add("Vat", dVat.ToString("#,##0.00"));
            fields.Add("ExcludeVatAmount", dTotalPrice.ToString("#,##0.00"));
            fields.Add("TotalPrice", dTotalPrice.ToString("#,##0.00"));
            fields.Add("ThaiBaht", ThaiBaht);
            fields.Add("DiscountPercent", "0.00");
            fields.Add("ExtraDiscount", "0.00");
            fields.Add("NetAmount", dIncludeVat.ToString("#,##0.00"));

            string strTemplatePath = Server.MapPath("~/Templates/ใบสั่งซื้อ.docx");
            string strSaveAsPath = Server.MapPath("~/Temp/");
            string strSaveAsFileName = string.Format("ใบสั่งซื้อ_{1}_{0}.docx", 
                DateTime.Now.ToString("yyyyMMddHHmmssffff"),
                model.Code);

            CreatePOTable(strTemplatePath, dics.Count, strSaveAsPath + strSaveAsFileName, 0 ,0  );
            if (mailMerge.MergeTemplate(strSaveAsPath + strSaveAsFileName, strSaveAsPath + strSaveAsFileName, fields, null, dics, null))
            {
                Response.Clear();
                Response.ContentType = "text/plain";
                Response.AddHeader("Content-Disposition", "attachment; filename=" + strSaveAsFileName);
                Response.WriteFile(strSaveAsPath + strSaveAsFileName);
                Response.Flush();
                if (true)
                {
                    System.IO.File.Delete(strSaveAsPath + strSaveAsFileName);
                }
                Response.End();
            }
        }

        public void ReportPoPurchaseContact(int PoId)
        {
            var service = new PurchaseOrderService.PurchaseOrderServiceClient();
            var modelInstall = service.SelectPoInstallment(new Models.ICM.PurchaseOrderInstallment { PoId = PoId }, new []{"PurchaseOrder"}).FirstOrDefault();
            var model = service.Select(new Models.ICM.PurchaseOrder { RecordStatus = "!D", PoId = PoId }, null).FirstOrDefault();
            var model1 = service.ContactPo(PoId);
            var model2 = service.PositionNamePo(PoId);
            //var model3 = service.PoItems(PoId);

            Dictionary<string, string> fields = new Dictionary<string, string>();
            MailMerge mailMerge = new MailMerge();

            fields.Add("PoCode", model.Code);

            if (modelInstall != null) //มีกรณีที่ค่า modelInstall เป็นค่าว่าง ทำให้เกิด Error
            {
                if (modelInstall.RequireDuration == null)
                {
                    fields.Add("DateLast", Convert.ToDateTime(modelInstall.RequireDate).ToLongDateString());
                }
                else
                {
                    fields.Add("DateLast", modelInstall.RequireDuration.ToString() + " วัน");
                }
            }

            fields.Add("PoDate" , Convert.ToDateTime(model.OrderDate).ToLongDateString());
            fields.Add("ContactName", model1[0]);
            fields.Add("CreatedBy", model2[0]);
            fields.Add("PositionName", model2[1]);
            fields.Add("OrderDate", model2[3]);
            fields.Add("RequireDuration", (model.RequireDuration != null ? model.RequireDuration.ToString() + " วัน" : ""));
            fields.Add("ReqDate", Convert.ToDateTime(model.RequireDate).ToLongDateString());

            var dics = service.PoItems(PoId).ToList();
            decimal dTotalPrice = 0;
            decimal dTotalVat = 0;
            foreach (var dic in dics)
            {
                foreach (var item in dic)
                {
                    if (item.Key == "SumPrice")
                    {
                        dTotalPrice += Convert.ToDecimal(item.Value);
                    }
                    if (item.Key == "VatAmount")
                    {
                        dTotalVat += Convert.ToDecimal(item.Value??"0");
                    }
                }
            }

            decimal dVat = dTotalPrice * 7 / 100;
            decimal dIncludeVat = dTotalPrice + dVat;
            //string ThaiBaht = service.ThaiBaht((dIncludeVat).ToString()); 
            var serviceBidderQ = new BidService.BidServiceClient();
            var ModelBidQs = serviceBidderQ.SelectBidderQuotation(
                new BidderQuotation
                {
                    BidderId = model.BidderId
                }, null);
            var ModelBidQ = ModelBidQs.FirstOrDefault();

            var netPrice = (ModelBidQs.Sum(o=>o.NetAmount) ?? 0);

            var netPriceStr = netPrice.ToString();
            if (ModelBidQ.VatAmount != 0)
            {
                string ThaiBaht = service.ThaiBaht(netPriceStr);
                fields.Add("Vat", dTotalVat.ToString("#,##0.00"));//;/dVat.ToString("#,##0.00"));
                fields.Add("ExcludeVatAmount", dIncludeVat.ToString("#,##0.00"));
                fields.Add("TotalPrice", dTotalPrice.ToString("#,##0.00"));
                fields.Add("ThaiBaht", ThaiBaht);
                fields.Add("DiscountPercent", (ModelBidQ.DiscountPercent ?? 0).ToString("#,##0.00"));
                fields.Add("ExtraDiscount", (ModelBidQ.ExtraDiscount ?? 0).ToString("#,##0.00"));
                fields.Add("NetAmount", netPrice.ToString("#,##0.00"));
                
            }
            else
            {
                string ThaiBaht = service.ThaiBaht(netPriceStr);
                fields.Add("Vat","0.00");
                fields.Add("ExcludeVatAmount", dTotalPrice.ToString("#,##0.00"));
                fields.Add("TotalPrice",  dTotalPrice.ToString("#,##0.00"));
                fields.Add("ThaiBaht", ThaiBaht);
                fields.Add("DiscountPercent", (ModelBidQ.DiscountPercent ?? 0).ToString("#,##0.00"));
                fields.Add("ExtraDiscount", (ModelBidQ.ExtraDiscount ?? 0).ToString("#,##0.00"));
                fields.Add("NetAmount", netPrice.ToString("#,##0.00"));
            }

            var serviceUser = new EmployeeService.EmployeeServiceClient();
            var modelEmp = serviceUser.Select(new Employee { EmpId = Class.GimsMembership.CurrentEmpId }
                , new[] { "Position.WorkingUpperPosition" }, true).FirstOrDefault();
            //กำหนดชื่อหัวหน้าที่จะแสดงตามยอดเงินเพิ่ม ณ วันที่ 28/5/57
            var upperPositionId = ((ModelBidQ.NetAmount ?? 0) < 100000 ? modelEmp.Position.WorkingUpperPosId : ((ModelBidQ.NetAmount ?? 0) < 500000 ? 56 : 32));
            var modelEmpHead = serviceUser.Select(new Employee { PosId = upperPositionId, RecordStatus = "!D" }
                , new[] { "MasNamePrefix" }, true).FirstOrDefault();             //ใส่ new[] { "MasNamePrefix" } เพื่อให้ FullName มีคำนำหน้าชื่อมาด้วย
            fields.Add("HeadName", (modelEmpHead.FullName ?? ""));

            var userService = new UserService.UserServiceClient();
            var modelPosition = userService.GetUsers().Where(o => o.EmpId == modelEmpHead.EmpId).FirstOrDefault();
            var posName = modelPosition.Employee != null && modelPosition.Employee.Position != null && modelPosition.Employee.Position.Name != null ? modelPosition.Employee.Position.Name : "";
            fields.Add("HeadPositionName", upperPositionId == 78 ? "หัวหน้าเจ้าหน้าที่พัสดุ" : posName);   //กำหนดไว้ชั่วคราวเนื่องจากคุณสุชาดา รหัสที่ 78 ตำแหน่งจริงเป็น หัวหน้างานไอที ระบบไม่สามารถกำหนดให้ 1 คนแสดง 2 ตำแหน่งได้

            string strTemplatePath = Server.MapPath("~/Templates/ใบสั่งจ้าง.docx");
            string strSaveAsPath = Server.MapPath("~/Temp/");
            string strSaveAsFileName = string.Format("ใบสั่งจ้าง_{1}_{0}.docx", 
                DateTime.Now.ToString("yyyyMMddHHmmssffff"),
                model.Code);

            CreatePOTable(strTemplatePath, dics.Count, strSaveAsPath + strSaveAsFileName, ModelBidQ.DiscountPercent ?? 0, ModelBidQ.ExtraDiscount ?? 0);
            if (mailMerge.MergeTemplate(strSaveAsPath + strSaveAsFileName, strSaveAsPath + strSaveAsFileName, fields, null, dics, null))
            {
                Response.Clear();
                Response.ContentType = "text/plain";
                Response.AddHeader("Content-Disposition", "attachment; filename=" + strSaveAsFileName);
                Response.WriteFile(strSaveAsPath + strSaveAsFileName);
                Response.Flush();
                if (true)
                {
                    System.IO.File.Delete(strSaveAsPath + strSaveAsFileName);
                }
                Response.End();
            }
        }

        public void CreatePOTable(string strTemplateFilePath, int row, string saveTemp, decimal discountPercent, decimal extraDiscount)
        {
            using (DocX document = DocX.Load(strTemplateFilePath))
            {
                if (document.Tables[2].ColumnCount > 5)
                {
                    //วนลูปรายการในตาราง
                    for (int i = 0; i < row; i++)
                    {
                        Row rx = document.Tables[2].InsertRow(document.Tables[2].RowCount);

                        rx.Cells[0].Paragraphs[0].Append("«" + "No" + (i + 1).ToString() + "»");
                        rx.Cells[0].Paragraphs[0].FontSize(14);
                        rx.Cells[0].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                        rx.Cells[1].Paragraphs[0].Append("«" + "ResName" + (i + 1).ToString() + "»");
                        rx.Cells[1].Paragraphs[0].FontSize(14);
                        rx.Cells[1].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                        rx.Cells[2].Paragraphs[0].Append("«" + "UnitName" + (i + 1).ToString() + "»");
                        rx.Cells[2].Paragraphs[0].Alignment = Alignment.center;
                        rx.Cells[2].Paragraphs[0].FontSize(14);
                        rx.Cells[2].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                        rx.Cells[3].Paragraphs[0].Append("«" + "Qty" + (i + 1).ToString() + "»");
                        rx.Cells[3].Paragraphs[0].Alignment = Alignment.center;
                        rx.Cells[3].Paragraphs[0].FontSize(14);
                        rx.Cells[3].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                        rx.Cells[4].Paragraphs[0].Append("«" + "Price" + (i + 1).ToString() + "»");
                        rx.Cells[4].Paragraphs[0].Alignment = Alignment.center;
                        rx.Cells[4].Paragraphs[0].FontSize(14);
                        rx.Cells[4].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                        rx.Cells[5].Paragraphs[0].Append("«" + "SumPrice" + (i + 1).ToString() + "»");
                        rx.Cells[5].Paragraphs[0].Alignment = Alignment.center;
                        rx.Cells[5].Paragraphs[0].FontSize(14);
                        rx.Cells[5].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                    }

                    //บรรทัดยอดรวมในตาราง
                    Row total = document.Tables[2].InsertRow(document.Tables[2].RowCount);
                    total.Cells[4].Paragraphs[0].Append("เป็นเงิน");
                    total.Cells[4].Paragraphs[0].Alignment = Alignment.center;
                    total.Cells[4].Paragraphs[0].FontSize(14);
                    total.Cells[4].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                    total.Cells[5].Paragraphs[0].Append("«" + "TotalPrice" + "»");
                    total.Cells[5].Paragraphs[0].Alignment = Alignment.center;
                    total.Cells[5].Paragraphs[0].FontSize(14);
                    total.Cells[5].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));

                    //บรรทัดภาษีมูลค่าเพิ่มในตาราง
                    Row vat = document.Tables[2].InsertRow(document.Tables[2].RowCount);
                    vat.Cells[4].Paragraphs[0].Append("VAT 7%");
                    vat.Cells[4].Paragraphs[0].Alignment = Alignment.center;
                    vat.Cells[4].Paragraphs[0].FontSize(14);
                    vat.Cells[4].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                    vat.Cells[5].Paragraphs[0].Append("«" + "Vat" + "»");
                    vat.Cells[5].Paragraphs[0].Alignment = Alignment.center;
                    vat.Cells[5].Paragraphs[0].FontSize(14);
                    vat.Cells[5].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));

                    if (discountPercent > 0)
                    {
                        //บรรทัดส่วนลด%
                        Row dP = document.Tables[2].InsertRow(document.Tables[2].RowCount);
                        dP.Cells[4].Paragraphs[0].Append("ส่วนลด %");
                        dP.Cells[4].Paragraphs[0].Alignment = Alignment.center;
                        dP.Cells[4].Paragraphs[0].FontSize(14);
                        dP.Cells[4].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                        dP.Cells[5].Paragraphs[0].Append("«" + "DiscountPercent" + "»");
                        dP.Cells[5].Paragraphs[0].Alignment = Alignment.center;
                        dP.Cells[5].Paragraphs[0].FontSize(14);
                        dP.Cells[5].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                    }

                    if (extraDiscount > 0)
                    {
                        //บรรทัดส่วนลดพิเศษ
                        Row dS = document.Tables[2].InsertRow(document.Tables[2].RowCount);
                        dS.Cells[4].Paragraphs[0].Append("ส่วนลดพิเศษ");
                        dS.Cells[4].Paragraphs[0].Alignment = Alignment.center;
                        dS.Cells[4].Paragraphs[0].FontSize(14);
                        dS.Cells[4].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                        dS.Cells[5].Paragraphs[0].Append("«" + "ExtraDiscount" + "»");
                        dS.Cells[5].Paragraphs[0].Alignment = Alignment.center;
                        dS.Cells[5].Paragraphs[0].FontSize(14);
                        dS.Cells[5].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                    }

                    //บรรทัดยอดเงินรวมท้ายตาราง
                    Row sum = document.Tables[2].InsertRow(document.Tables[2].RowCount);
                    sum.MergeCells(0, 3);
                    //หลังการเมิจภายในฟิลด์จะเกิดช่องว่างหากใส่ข้อมูลเลยจะทำให้ช่องกว้างกว่าปกติ จึงต้องทำการเคีลยร์ก่อน
                    int paraCount1 = sum.Cells[0].Paragraphs.Count;

                    for (int k = 1; k < paraCount1; k++)
                    {
                        sum.Cells[0].Paragraphs[0].Remove(false);
                    }

                    sum.Cells[0].Paragraphs[0].Append("«" + "ThaiBaht" + "»");
                    sum.Cells[0].Paragraphs[0].Alignment = Alignment.center;
                    sum.Cells[0].Paragraphs[0].FontSize(14);
                    sum.Cells[0].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                    sum.Cells[1].Paragraphs[0].Append("รวมเป็นเงิน");
                    sum.Cells[1].Paragraphs[0].Alignment = Alignment.center;
                    sum.Cells[1].Paragraphs[0].FontSize(14);
                    sum.Cells[1].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                    sum.Cells[2].Paragraphs[0].Append("«" + "NetAmount" + "»");
                    sum.Cells[2].Paragraphs[0].Alignment = Alignment.center;
                    sum.Cells[2].Paragraphs[0].FontSize(14);
                    sum.Cells[2].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));

                    document.SaveAs(saveTemp);
                }
            }
        }

        public ActionResult Return(int PoId)
        {
            StringBuilder message_error = new StringBuilder();

            try
            {
                using (var service = new PurchaseOrderService.PurchaseOrderServiceClient())
                {
                    using (var service2 = new PurchaseRequestItemService.PurchaseRequestItemServiceClient())
                    {
                        using (var service3 = new ProjectOperationService.ProjectOperationServiceClient())
                        {
                            var Po = service.Select(new PurchaseOrder() { PoId = PoId }, new[] { "Bidder" }).FirstOrDefault();
                            var PrItems = service2.Select(new PurchaseRequestItem() { PrId = Po.Bidder.PrId }, null);

                            foreach (var PrItem in PrItems)
                            {
                                service3.UpdateProjectOperationReportToZero(PrItem.PrItemId, Class.GimsMembership.CurrentEmpId);
                            }
                        }
                    }
                }

                message_error.Append("<div class='alert alert-error'>");
                message_error.Append("<button type='button' class='close' data-dismiss='alert'>");
                message_error.Append("<i class='icon-remove'></i>");
                message_error.Append("</button>");
                message_error.Append("<strong>");
                message_error.Append("<i class='icon-remove'></i>");
                message_error.Append(" บันทึกเรียบร้อย");
                message_error.Append("</strong>");

                return Json(new { success = true, message = message_error.ToString() }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                message_error.Append("<div class='alert alert-error'>");
                message_error.Append("<button type='button' class='close' data-dismiss='alert'>");
                message_error.Append("<i class='icon-remove'></i>");
                message_error.Append("</button>");
                message_error.Append("<strong>");
                message_error.Append("<i class='icon-remove'></i>");
                message_error.Append(" พบข้อผิดพลาด: ");
                message_error.Append("</strong>");
                ModelState.AddModelError(ErrorLocalization.Error, ErrorLocalization.ErrorWhileDeleteData);
                GerpLog.Error(ErrorLocalization.ErrorWhileDeleteData + ", " + ex.Message);
                return Json(new { success = false }, JsonRequestBehavior.AllowGet);
            }
        }
    }
}